package com.example.lcl.exp2019_2;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

public class DBAdapter {
    //数据库基本信息
    private static final String DB_NAME="people.db";   //数据库名
    private static final String DB_TABLE="info";  //表名
    private static final int   DB_VERSION=1;	  //数据库版本
    //字段名称
    public static  final  String KEY_ID="id";
    public static  final  String KEY_NAME="name";
    public static  final  String KEY_AGE="age";
    public static  final  String KEY_HEIGHT="height";

    private SQLiteDatabase db=null;  //数据库对象
    private Context context; //记住由构造函数传入的上下文对象

    //数据库辅助操作对象
    private MyDBOpenHelper helper;

    public DBAdapter(Context context){
        this.context=context;  //记住由构造函数传入的上下文对象
        helper=new MyDBOpenHelper(context, DB_NAME, null, DB_VERSION); //若在此处更改数据库版本,则会触发升级动作
    }

    //创建/打开数据库
    public boolean createDB(){
        try{
            //打开/创建指定文件夹下的数据库,需要相应路径的写权限
            db=helper.getWritableDatabase();
        }catch(Exception e){
            return false;
        }
        return true;
    }

    //添加新数据,插入3条记录
    public boolean insert(People p){
        ContentValues cv=new ContentValues();

        //设置插入的字段数值,对于自增的字段不需要设置
        cv.put(KEY_NAME, p.getName());
        cv.put(KEY_AGE, p.getAge());
        cv.put(KEY_HEIGHT, p.getHeight());
        if (db.insert(DB_TABLE, null, cv)==-1)
            return false;

        return true;
    }

    //查询数据
    public List<People> QueryByAge(int age1){
        List<People> peopleList = new ArrayList<>();

        //查询---可以使用占位符--SQL中的?对应后面字符串数组中的字符串,此处不用,第二个参数设置为null
        Cursor cursor = db.query(DB_TABLE,new String[]{KEY_ID,KEY_NAME,KEY_AGE,KEY_HEIGHT},
                KEY_AGE+"=?",new String[]{String.valueOf(age1)},null,null,null);
        //如果要查询的表不存在则会产生异常
        if (cursor.getCount()==0) //查询结果为空则返回
            return peopleList;
        while (cursor.moveToNext()){ //循环取出游标中的查询结果
            //取出本行各列的值
            int id=cursor.getInt(0);
            String name=cursor.getString(1);
            int age=cursor.getInt(2);
            float height=cursor.getFloat(3);
            People people = new People(id,name,age,height);
            peopleList.add(people);
        }//继续取下一行

        cursor.close(); //关闭游标

        return peopleList;
    }

    //查询全部数据
    public ArrayList<People> QueryAll() {

        ArrayList<People> list = new ArrayList<>();
        Cursor cursor=db.query(DB_TABLE,new String[]{KEY_ID,KEY_NAME,KEY_AGE,KEY_HEIGHT},
                null,null,null,null,null);
        if (cursor.getCount() == 0) {
            People people = new People(-1,"error",-1,-1);
            list.add(people);
            return list;
        }

        while (cursor.moveToNext()) {
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            int age = cursor.getInt(2);
            float height = cursor.getFloat(3);
            People people = new People(id,name,age,height);
            list.add(people);
        }

        cursor.close();
        return list;
    }

    //修改
    public boolean update(People people){
        ContentValues cv=new ContentValues();
        cv.put(KEY_NAME, people.getName()); //设定要修改的值
        cv.put(KEY_AGE, people.getAge()); //设定要修改的值
        cv.put(KEY_HEIGHT, people.getHeight()); //设定要修改的值
        if (db.update(DB_TABLE, cv, KEY_ID+"=?",new String[]{String.valueOf(people.getId())})>0)
            return true;
        else
            return false;
    }

    //删除
    public boolean delete(int id){
        if (db.delete(DB_TABLE, KEY_ID+"=?", new String[]{String.valueOf(id)})>0)
            return true;
        else
            return false;
    }

    //关闭数据库对象
    public void close(){
        if (db!=null){
            db.close();
            db=null;
        }
    }

//    //完成事务
//    public  void execTransaction(boolean rollback){
//        db.beginTransaction();  //开始事务
//        update();
//        delete(); //全部删除数据
//        if (!rollback) //模拟检测条件，如果不回滚则设置事务完成,在结束事务时会正确提交
//            db.setTransactionSuccessful(); //如不执行此项,则在结束事务时会回滚事务
//        db.endTransaction(); //结束事务
//    }
}
